﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DTO;

namespace DAO
{
    public class PhieuKhamBenhDAO
    {
        public static DataTable LayDanhSachPhieuKhamBenh()
        {
            string sql;
            sql = "SELECT * FROM PhieuKhamBenh";
            return DataAccess.ExecQuery(sql);
        }

        public static int ThemPhieuKhamBenh(PhieuKhamBenhDTO obj)
        {
            string sql;
            sql = string.Format(@"INSERT INTO PhieuKhamBenh(NgayKham, NhanVienPhuTrach, MaHoSoBenhNhan, ThanhToanMacDinh) VALUES(N'{0}', N'{1}', N'{2}', N'{3}')",
                                    obj.NgayKham, obj.NhanVienPhuTrach, obj.MaHoSoBenhNhan, obj.ThanhToanMacDinh);
            DataAccess.ExecNonQuery(sql);

            sql = "update PhieuKhamBenh set MaPhieu = 'PKB' + CAST(MaHoSoBenhNhan as nvarchar(10))";
            DataAccess.ExecNonQuery(sql);

            sql = "select max(MaPKB) from PhieuKhamBenh";
            DataTable dt = DataAccess.ExecQuery(sql);
            int max = Convert.ToInt32(dt.Rows[0][0]);
            return max;
        }

        public static void SuaPhieuKhamBenh(PhieuKhamBenhDTO obj)
        {
            string sql;
            sql = string.Format(@"UPDATE PhieuKhamBenh SET NgayKham = N'{0}', NhanVienPhuTrach = N'{1}', MaHoSoBenhNhan = N'{2}'
                WHERE MaPKB = '{3}'", obj.NgayKham, obj.NhanVienPhuTrach, obj.MaHoSoBenhNhan, obj.MaPKB);
            DataAccess.ExecNonQuery(sql);
        }

        public static void XoaPhieuKhamBenh(int maPhieuKhamBenh)
        {
            string sql;
            sql = string.Format("DELETE PhieuKhamBenh WHERE MaPKB = {0}", maPhieuKhamBenh);
            DataAccess.ExecNonQuery(sql);
        }

        public static PhieuKhamBenhDTO LayThongTinPhieuKhamBenhTheoMaPhieuKhamBenh(int maPhieuKhamBenh)
        {
            PhieuKhamBenhDTO kq = new PhieuKhamBenhDTO();
            string sql;
            sql = @"select * from PhieuKhamBenh where MaPKB = " + maPhieuKhamBenh;
            DataTable dt = DataAccess.ExecQuery(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                kq.MaPKB = Convert.ToInt32(dt.Rows[0]["MaPKB"]);
                kq.NgayKham = Convert.ToDateTime(dt.Rows[0]["NgayKham"]);
                kq.NhanVienPhuTrach = Convert.ToString(dt.Rows[0]["NhanVienPhuTrach"]);
                kq.MaHoSoBenhNhan = Convert.ToInt32(dt.Rows[0]["MaHoSoBenhNhan"]);
            }
            return kq;
        }

        public static DataTable KiemTraPhieuKhamBenhTrongNgay(int maHSBN)
        {
            string sql;
            sql = "SELECT * FROM PhieuKhamBenh where MaHoSoBenhNhan = " + maHSBN + " and YEAR(NgayKham) = YEAR(GetDate()) and MONTH(NgayKham) = MONTH(GetDate()) and DAY(NgayKham) = DAY(GetDate())";
            return DataAccess.ExecQuery(sql);
        }

        public static DataTable TimKiemThanhToanMacDinh(string sql)
        {
            return DataAccess.ExecQuery(sql);
        }

        public static void ThanhToanMacDinh(string maphieu, string sotien)
        {
            string sql = "update PhieuKhamBenh set ThanhToanMacDinh = " + sotien + " where MaPhieu = '" + maphieu + "'";
            DataAccess.ExecNonQuery(sql);
        }

        public static DataTable PhieuKhamBenhTrongNgayDaThanhToan()
        {
            string sql = @"select MaPKB, MaPhieu, HoTen from PhieuKhamBenh, HoSoBenhNhan
                        where PhieuKhamBenh.MaHoSoBenhNhan = HoSoBenhNhan.MaHoSoBenhNhan and 
                        ThanhToanMacDinh > 0 and DaKham = 0 and YEAR(NgayKham) = YEAR(GetDate()) and MONTH(NgayKham) = MONTH(GetDate()) and DAY(NgayKham) = DAY(GetDate())";
            return DataAccess.ExecQuery(sql);
        }

    }
}
